
package dao;

import controller.FornecedorController;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import model.AtributosMODEL;
import model.CalcadoMODEL;
import model.CalcadosFiltradosMODEL;
import model.FornecedorMODEL;
import org.postgresql.util.PSQLException;

/**
 *
 * @author Welisson
 */
public class CalcadoDAO extends GenericDAO{
    private static final String INSERT = "INSERT INTO calcado( for_id, cat_id, cal_nome, cal_genero, cal_valor, cal_detalhes) "
            + "VALUES (?, ?, ?, ?, ?, ?);";
    private static final String UPDATE = "UPDATE calcado SET for_id=?, cat_id=?, cal_nome=?, cal_genero=?, cal_valor=?, "
            + "cal_detalhes=? WHERE cal_id = ?;";
    private static final String DELETE = "DELETE FROM calcado WHERE cal_id = ?;";
    private static final String SELECT = "SELECT * FROM calcado ORDER BY cal_nome ASC;";
    private static final String SELECT_BY_MARCA = "SELECT * FROM calcado WHERE for_id = ? ORDER BY cal_nome ASC;";
    
    private static final String INSERT_ATRIBUTO = "INSERT INTO atributos( cal_id, cor_id, num_id, cal_qtd) VALUES (?, ?, ?, ?);";
    private static final String DELETE_ATRIBUTO = "DELETE FROM atributos WHERE cal_id = ?;";
    private static final String SELECT_ATRIBUTO = "SELECT * FROM atributos;";
    
    
    public int insert(CalcadoMODEL calcado, List<AtributosMODEL> atributos) throws SQLException{
        int id = -1;
        
        PreparedStatement ps = conn.prepareStatement(INSERT, PreparedStatement.RETURN_GENERATED_KEYS);
        conn.setAutoCommit(false);
        ResultSet rs;
        
        try{
            ps.setInt(1, calcado.getFornecedor().getId());
            ps.setInt(2, calcado.getCategoria());
            ps.setString(3, calcado.getNome());
            ps.setString(4, calcado.getGenero());
            ps.setDouble(5, calcado.getValor());
            ps.setString(6, calcado.getDetalhes());
            ps.executeUpdate();
            rs = ps.getGeneratedKeys();
            
            while(rs.next()){
                id = rs.getInt(1);
            }
            
            if(!atributos.isEmpty()){
                
                for(AtributosMODEL atr : atributos){
                    ps = conn.prepareStatement(INSERT_ATRIBUTO);
                    ps.setInt(1, id);
                    ps.setInt(2, atr.getCor());
                    ps.setInt(3, atr.getNum());
                    ps.setInt(4, atr.getQtd());
                    ps.execute();
                }
            }
            
            conn.commit();
        }catch(PSQLException ex){
            ex.printStackTrace(System.out);
            conn.rollback();
            id = -1;
        }
        
        conn.setAutoCommit(true);
        ps.close();
        conn.close();
        return id;
    }
    
    public boolean update(CalcadoMODEL calcado, List<AtributosMODEL> atributos) throws SQLException{
        PreparedStatement ps = conn.prepareStatement(UPDATE);
        conn.setAutoCommit(false);
        boolean ok = false;
            
        try{
            ps.setInt(1, calcado.getFornecedor().getId());
            ps.setInt(2, calcado.getCategoria());
            ps.setString(3, calcado.getNome());
            ps.setString(4, calcado.getGenero());
            ps.setDouble(5, calcado.getValor());
            ps.setString(6, calcado.getDetalhes());
            ps.setInt(7, calcado.getId());
            ps.execute();
            
            ps = conn.prepareStatement(DELETE_ATRIBUTO);
            ps.setObject(1, calcado.getId());
            ps.execute();
            
            if(!atributos.isEmpty()){
                
                for(AtributosMODEL atr : atributos){
                    ps = conn.prepareStatement(INSERT_ATRIBUTO);
                    ps.setInt(1, atr.getCalcado());
                    ps.setInt(2, atr.getCor());
                    ps.setInt(3, atr.getNum());
                    ps.setInt(4, atr.getQtd());
                    ps.execute();
                }
            }
            
            conn.commit();
            ok = true;
        }catch(PSQLException ex){
            ex.printStackTrace(System.out);
            conn.rollback();
        }
        conn.setAutoCommit(true);
        ps.close();
        conn.close();
        return ok;
    }
    
    public boolean delete(int id) throws SQLException{
        PreparedStatement ps = conn.prepareStatement(DELETE_ATRIBUTO);
        boolean ok = false;
        conn.setAutoCommit(false);
        
        try{
            ps.setInt(1, id);
            ps.execute();
            
            ps = conn.prepareStatement(DELETE);
            ps.setInt(1, id);
            ps.execute();
            
            conn.commit();
            ok = true;
        }catch(PSQLException ex){
            ex.printStackTrace(System.out);
            conn.rollback();
            
        }
        conn.setAutoCommit(true);
        ps.close();
        conn.close();
        return ok;
    }
    
    public List<CalcadoMODEL> select() throws SQLException{
        List<CalcadoMODEL> calcados = new ArrayList<>();
        
        PreparedStatement ps = conn.prepareStatement(SELECT);
        ResultSet rs = ps.executeQuery();
        
        FornecedorController objFornecedor = new FornecedorController();
        
        
        while(rs.next()){
            CalcadoMODEL cal = new CalcadoMODEL();
            cal.setId(rs.getInt("cal_id"));
            cal.setFornecedor(objFornecedor.findFornecedorById(rs.getInt("for_id")));
            cal.setCategoria(rs.getInt("cat_id"));
            cal.setNome(rs.getString("cal_nome"));
            cal.setGenero(rs.getString("cal_genero"));
            cal.setValor(rs.getDouble("cal_valor"));
            cal.setDetalhes(rs.getString("cal_detalhes"));
            
            calcados.add(cal);
        }
        
        rs.close();
        ps.close();
        conn.close();
        return calcados;
    }
    
    public List<CalcadoMODEL> selectByMarca(int marca) throws SQLException{
        List<CalcadoMODEL> calcados = new ArrayList<>();
        
        PreparedStatement ps = conn.prepareStatement(SELECT_BY_MARCA);
        
        ps.setInt(1, marca);
        
        ResultSet rs = ps.executeQuery();
        
        FornecedorController objFornecedor = new FornecedorController();
        
        while(rs.next()){
            CalcadoMODEL cal = new CalcadoMODEL();
            cal.setId(rs.getInt("cal_id"));
            cal.setFornecedor(objFornecedor.findFornecedorById(rs.getInt("for_id")));
            cal.setCategoria(rs.getInt("cat_id"));
            cal.setNome(rs.getString("cal_nome"));
            cal.setGenero(rs.getString("cal_genero"));
            cal.setValor(rs.getDouble("cal_valor"));
            cal.setDetalhes(rs.getString("cal_detalhes"));
            
            calcados.add(cal);
        }
        
        rs.close();
        ps.close();
        conn.close();
        return calcados;
    }

    public List<CalcadosFiltradosMODEL> calcadosfilter(String query, List<Object> dados) throws SQLException {
        List<CalcadosFiltradosMODEL> calcados = new ArrayList<>();
        
        PreparedStatement ps = conn.prepareStatement(query);
        
        int i = 1;
        for(Object d : dados){
            ps.setObject(i++, d);
            
        }
        
        ResultSet rs = ps.executeQuery();
        
        while(rs.next()){
            CalcadosFiltradosMODEL cal = new CalcadosFiltradosMODEL();
            cal.setCalcado(rs.getInt("cal_id"));
            cal.setNome(rs.getString("cal_nome"));
            cal.setGenero(rs.getString("cal_genero"));
            cal.setValor(rs.getDouble("cal_valor"));
            cal.setCategoria(rs.getString("cat_categoria"));
            cal.setQuantidade(rs.getInt("cal_qtd"));
            cal.setCor(rs.getString("cor_cores"));
//            cal.setRazao(rs.getString("for_razao"));
            cal.setDe(rs.getInt("num_de"));
            cal.setAte(rs.getInt("num_ate"));
            
            calcados.add(cal);
        }
        
        rs.close();
        ps.close();
        conn.close();
        return calcados;
    }
    
}

